﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using UygulamaYetkiService.Classes;

namespace UygulamaYetkiService
{
    public class KullaniciDAO : IDisposable
    {
        public Kullanici KullaniciKaydet(Kullanici kullanici)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    con.Open();
                    SqlTransaction transaction = con.BeginTransaction("trans");
                    try
                    {
                        cmd.CommandText = @"DECLARE @password VARBINARY(2000)  
                                            SET @password = HashBytes('MD5','123abc.') 
                                            insert into Kullanici with(updlock) (Kodu,Adi,Soyadi,sifre,GrupRef,AktifMi,KayitTarih) values (@Kodu,@Adi,@Soyadi,@password,@GrupRef,@AktifMi,GetDate()); SELECT SCOPE_IDENTITY()";
                        cmd.Parameters.Add("@Kodu", kullanici.Kodu);
                        cmd.Parameters.Add("@Adi", kullanici.Adi);
                        cmd.Parameters.Add("@Soyadi", kullanici.Soyadi);
                        cmd.Parameters.Add("@GrupRef", kullanici.GrupID);
                        cmd.Parameters.Add("@AktifMi", kullanici.AktifMi);
                        cmd.Connection = con;
                        cmd.Transaction = transaction;
                        kullanici.ID = Convert.ToInt64(cmd.ExecuteScalar());
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        using (ExceptionLogDao el = new ExceptionLogDao())
                        {
                            el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                        }
                    }
                    finally
                    {
                        con.Close();
                        con.Dispose();
                        cmd.Dispose();
                    }
                    return kullanici;
                }
            }
        }

        public Kullanici KullaniciGiris(string kullaniciKodu, string kullaniciSifre)
        {
            Kullanici kullanici = null;
            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
                {
                    string sql = @" DECLARE @password VARBINARY(2000) " +
                    " SET @password = HashBytes('MD5', '" + kullaniciSifre + "') " +
                    " select k.ID,k.Kodu,k.Adi,k.Soyadi,k.Sifre,k.GrupRef,g.Adi as GrupAdi,k.AktifMi, " +
                    "        case k.AktifMi " +
                    "             when 1 then 'Aktif' " +
                    "             when 2 then 'Pasif' " +
                    "        end as AktifMiAciklama " +
                    " from Kullanici k with(nolock) " +
                    " inner join Grup g with(nolock) on k.GrupRef=g.ID " +
                    " where k.Kodu = '" + kullaniciKodu + "' and " +
                    "       k.Sifre = @password ";
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            cmd.CommandTimeout = 60000;
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            if (dt.Rows.Count == 1)
                                kullanici = new Kullanici(dt.Rows[0]);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return kullanici;
        }

        public Kullanici KullaniciGuncelle(Kullanici kullanici)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    con.Open();
                    SqlTransaction transaction = con.BeginTransaction("trans");
                    try
                    {
                        cmd.CommandText = "update Kullanici with(updlock) set Kodu=@Kodu,Adi=@Adi,Soyadi=@Soyadi,GrupRef=@GrupRef,AktifMi=@AktifMi where ID=@ID;";
                        cmd.Parameters.Add("@Kodu", kullanici.Kodu);
                        cmd.Parameters.Add("@Adi", kullanici.Adi);
                        cmd.Parameters.Add("@Soyadi", kullanici.Soyadi);
                        cmd.Parameters.Add("@GrupRef", kullanici.GrupID);
                        cmd.Parameters.Add("@AktifMi", kullanici.AktifMi);
                        cmd.Parameters.Add("@ID", kullanici.ID);
                        cmd.Connection = con;
                        cmd.Transaction = transaction;
                        kullanici.ID = Convert.ToInt64(cmd.ExecuteScalar());
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        using (ExceptionLogDao el = new ExceptionLogDao())
                        {
                            el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                        }
                    }
                    finally
                    {
                        con.Close();
                        con.Dispose();
                        cmd.Dispose();
                    }
                    return kullanici;
                }
            }
        }

        public bool KullaniciSifreGuncelle(long ID, string sifre)
        {
            bool sonuc = false;
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    con.Open();
                    SqlTransaction transaction = con.BeginTransaction("trans");
                    try
                    {
                        cmd.CommandText = " DECLARE @password VARBINARY(2000) " +
                        " SET @password = HashBytes('MD5','" + sifre + "') " +
                        " update Kullanici with(updlock) set Sifre=@password where ID=@ID;";
                        cmd.Parameters.Add("@ID", ID);
                        cmd.Connection = con;
                        cmd.Transaction = transaction;
                        cmd.ExecuteScalar();
                        transaction.Commit();
                        sonuc = true;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        using (ExceptionLogDao el = new ExceptionLogDao())
                        {
                            el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                        }
                        sonuc = false;
                    }
                    finally
                    {
                        con.Close();
                        con.Dispose();
                        cmd.Dispose();
                    }
                    return sonuc;
                }
            }
        }

        public Kullanici KullaniciSorgula(string kullaniciKodu)
        {
            Kullanici kullanici = null;
            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
                {
                    string sql = string.Format(@"select k.ID,k.Kodu,k.Adi,k.Soyadi,k.Sifre,k.GrupRef,g.Adi as GrupAdi,k.AktifMi,
                                                    case k.AktifMi
                                                         when 1 then 'Aktif'
                                                         when 2 then 'Pasif'
                                                     end as AktifMiAciklama
                                             from Kullanici k with(nolock)  
                                             inner join Grup g with(nolock) on k.GrupRef=g.ID 
                                             where k.Kodu='{0}' 
                                             order by k.Adi,k.Soyadi", kullaniciKodu);
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            cmd.CommandTimeout = 60000;
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            if (dt.Rows.Count == 1)
                                kullanici = new Kullanici(dt.Rows[0]);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return kullanici;
        }

        public List<Kullanici> KullaniciGetir()
        {
            List<Kullanici> kullaniciList = null;
            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
                {
                    string sql = @"select k.ID,k.Kodu,k.Adi,k.Soyadi,k.Sifre,k.GrupRef,g.Adi as GrupAdi,k.AktifMi,
                                      case k.AktifMi
                                           when 1 then 'Aktif'
                                           when 2 then 'Pasif'
                                      end as AktifMiAciklama
                               from Kullanici k with(nolock)  
                               inner join Grup g with(nolock) on k.GrupRef=g.ID 
                               order by k.Adi,k.Soyadi";
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            kullaniciList = new List<Kullanici>();
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            cmd.CommandTimeout = 60000;
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            if (dt.Rows.Count > 0)
                            {
                                foreach (DataRow dr in dt.Rows)
                                {
                                    Kullanici kullanici = new Kullanici(dr);
                                    kullaniciList.Add(kullanici);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return kullaniciList;
        }

        public List<DonusTip> AktifKullaniciGetir()
        {
            List<DonusTip> donusTipList = null;
            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
                {
                    string sql = @"select k.ID, k.Kodu + ' (' + k.Adi + ' ' + k.Soyadi + ')' KodAdSoyad
                                   from Kullanici k with(nolock)  
                                   where k.Aktifmi = 1
                                   order by k.Kodu";
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            donusTipList = new List<DonusTip>();
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            cmd.CommandTimeout = 60000;
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            if (dt.Rows.Count > 0)
                            {
                                foreach (DataRow dr in dt.Rows)
                                {
                                    DonusTip donusTip = new DonusTip();
                                    donusTip.ID = Convert.ToInt64(dr["ID"]);
                                    donusTip.Deger = dr["KodAdSoyad"].ToString();
                                    donusTipList.Add(donusTip);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return donusTipList;
        }

        public Kullanici KullaniciGetirbyID(long id)
        {
            Kullanici kullanici = null;
            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
                {
                    string sql = @"select k.ID,k.Kodu,k.Adi,k.Soyadi,k.Sifre,k.GrupRef,g.Adi as GrupAdi,k.AktifMi,
                                      case k.AktifMi
                                           when 1 then 'Aktif'
                                           when 2 then 'Pasif'
                                      end as AktifMiAciklama
                               from Kullanici k with(nolock)  
                               inner join Grup g with(nolock) on k.GrupRef=g.ID 
                               where k.ID=@ID
                               order by k.Adi,k.Soyadi";
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            cmd.Parameters.Add("@ID", id);
                            sda.SelectCommand = cmd;
                            cmd.CommandTimeout = 60000;
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            if (dt.Rows.Count == 1)
                                kullanici = new Kullanici(dt.Rows[0]);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return kullanici;
        }

        public bool KullaniciVarMiKontrol(string kodu)
        {
            bool sonuc = false;
            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
                {
                    string sql = @"select k.ID,k.Kodu,k.Adi,k.Soyadi,k.Sifre,k.GrupRef,g.Adi as GrupAdi,k.AktifMi,
                                      case k.AktifMi
                                           when 1 then 'Aktif'
                                           when 2 then 'Pasif'
                                      end as AktifMiAciklama
                               from Kullanici k with(nolock)  
                               inner join Grup g with(nolock) on k.GrupRef=g.ID 
                               where k.Kodu=@kodu
                               order by k.Adi,k.Soyadi";
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            cmd.Parameters.Add("@kodu", kodu);
                            sda.SelectCommand = cmd;
                            cmd.CommandTimeout = 60000;
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            if (dt.Rows.Count > 0)
                                sonuc = true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return sonuc;
        }

        public void Dispose()
        {
        }
    }
}




